package rgbos.forum.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import rgbos.forum.domain.Auditor;
import rgbos.forum.domain.User;

public class SQLUser{
	
	protected final int REGULER = 0;
	protected final int MODERATOR = 1;
	protected final int ADMIN = 2;
	protected final int OFFLINE = 0;
	protected final int ONLINE = 1;
	
	public boolean registerNewUser(User newUser){
		if (checkExistUser(newUser.getUsername())){
			Auditor.Log("The user " + newUser.getUsername() + " was Registered AllReady.", this.getClass());
    		return false;
		}
		
		Statement st = null;
		int val = 0;
		try {
			st = SQLUtil.conn_.createStatement();
			String username = newUser.getUsername();
			String password = newUser.getPassword();
			String email = newUser.getEmail();
			int perGroup = newUser.getPermmisionGroup();
			val = st.executeUpdate("INSERT INTO users (username,password,email,permGroup,isOnline)" +
			"VALUES('" + username + "','"+ password + "','" + email + "','" + perGroup +"',0)");
		} catch (SQLException e) {
			Auditor.Log("the user "+newUser.getUsername()+" got error in sql while trying to register", this.getClass());
			System.out.println(e.getMessage());
			return false;
		}
		
	    if ( val > 0 ){
	    	Auditor.Log("the user "+newUser.getUsername()+" was succsesfully registerd", this.getClass());
	    	return true;
	    }
	    else return false;
	}
	
	public boolean logIn(String userName, String pass){
		if (!checkExistUser(userName)){
			Auditor.Log("The user " + userName + " wasn't registered.", this.getClass());
    		return false;
		}
		try {
			java.sql.Statement stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT password FROM users WHERE username = '"+userName +"'");
	        while(rs.next()) {
	             if ( pass.equals(rs.getString("password"))){
	            	 setOnlineBit(userName,ONLINE);
	            	 return true;
	             }
	         }
	        return false;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean setOnlineBit(String userName, int onlineBit) {
		if (onlineBit == 1 || onlineBit == 0){
			java.sql.Statement stmt;
			try {
				stmt = SQLUtil.conn_.createStatement();
				
				int rs = stmt.executeUpdate
		        ("UPDATE users SET isOnline='"+onlineBit+"'WHERE username='"+userName + "'");
				if (rs > 0){
					if (onlineBit == ONLINE)
						Auditor.Log("The user " + userName + " was set to online.", this.getClass());
					else
						Auditor.Log("The user " + userName + " was set to offline.", this.getClass());
					return true;
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return false;
			}
		}
		return false;
	}
	public int getOnlineBit(String userName) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT isOnline FROM users WHERE username='"+userName+"'");
	        while (rs.next()){
	        	return rs.getInt("isOnline");
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return -1;
		}
        return -1;
	}
	public boolean modifyUserDetails(User user){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			String username = user.getUsername();
			String password = user.getPassword();
			String email = user.getEmail();
			int perGroup = user.getPermmisionGroup();
			int onlineBit = getOnlineBit(username);
			
			int rs = stmt.executeUpdate
	        ("UPDATE users SET password='"+password+"',email='"+email+"',permGroup='"+perGroup+"',isOnline='"+onlineBit+"'"+ 
	        		"WHERE username='"+username + "'");
			if (rs > 0){
				Auditor.Log("The user " + user.getUsername() + " was modified.", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + user.getUsername() + " was not Registered.", this.getClass());
        return false;
	}
	
	public boolean deleteUser(String username){
//		DELETE FROM Persons
//		WHERE LastName='Tjessem' AND FirstName='Jakob'
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("DELETE FROM users WHERE username='"+username+"'");
			if (rs > 0){
				Auditor.Log("The user " + username + " was deleted from the forum.", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username + " was not Registered.", this.getClass());
		return false;
	}
	
	public boolean setModerator(String username){
		if (!checkExistUser(username)){
			Auditor.Log("The user " + username + " wasn't registered.", this.getClass());
    		return false;
		}
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE users SET permGroup='"+MODERATOR+"'"+
	        		"WHERE username='"+username + "'");
			if (rs > 0){
				Auditor.Log(username + " is set to be a MODERATOR", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username +" isn\'t moderator ", this.getClass());
        return false;
	}
	
	public boolean setAdmin(String username){
		if (!checkExistUser(username)){
			Auditor.Log("The user " + username + " wasn't registered.", this.getClass());
    		return false;
		}
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE users SET permGroup='"+ADMIN+"'"+
	        		"WHERE username='"+username + "'");
			if (rs > 0){
				Auditor.Log(username + " is set to be a ADMIN", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username +" isn\'t admin ", this.getClass());
        return false;
	}
	
	public boolean checkExistUser(String username){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			ResultSet rs = stmt.executeQuery
			("SELECT username FROM users WHERE username = '"+username +"'");
			return (rs.first());
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean changePermission(String username, int value) {
		if (!checkExistUser(username)){
			Auditor.Log("The user " + username + " wasn't registered.", this.getClass());
    		return false;
		}
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE users SET permGroup="+value+" WHERE username='"+username + "'");
			if (rs > 0){
				Auditor.Log(username + " is set to be a ADMIN", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username +" isn\'t register ", this.getClass());
        return false;
	}
	
	public boolean checkAdmin(String username){
		if (!checkExistUser(username)){
			Auditor.Log("The user " + username + " wasn't registered.", this.getClass());
    		return false;
		}
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users WHERE permGroup='"+ADMIN+"' AND username='"+username+"'");
			if (rs.next()){
				Auditor.Log(username + " is ADMIN", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username +" isn\'t admin ", this.getClass());
        return false;
	}
	
	public boolean checkModerator(String username){
		if (!checkExistUser(username)){
			Auditor.Log("The user " + username + " wasn't registered.", this.getClass());
    		return false;
		}
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users WHERE permGroup='"+MODERATOR+"' AND username='"+username+"'");
			if (rs.next()){
				Auditor.Log(username + " is a MODERATOR", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		Auditor.Log("The user " + username +" isn\'t moderator ", this.getClass());
        return false;
	}
	
	public Vector<User> getUsers(){
		Vector<User> result = new Vector<User>();
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users");
	        while (rs.next()){
	        	User user = new User(rs.getString("username"),
	        	rs.getString("password"), rs.getString("email"), rs.getInt("permGroup"));
	        	result.add(user);
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
        return result;
	}
	
	public Vector<User> getOnlineUsers(){
		Vector<User> result = new Vector<User>();
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users WHERE isOnline=1");
	        while (rs.next()){
	        	User user = new User(rs.getString("username"),
	        	rs.getString("password"), rs.getString("email"), rs.getInt("permGroup"));
	        	result.add(user);
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
        return result;
	}
	
	public User getUserByName(String username) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users WHERE username='" + username + "'");
	        while (rs.next()){
	        	User user = new User(rs.getString("username"),
	        	rs.getString("password"), rs.getString("email"), rs.getInt("permGroup"));
	        	return user;
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());			
		}
		return null;
	}
	public String getEmailByName(String userName) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT email FROM users WHERE username='" + userName + "'");
	        while (rs.next()){
	        	return rs.getString("email");
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());			
		}
		return "";
	}
	public int[] getHourStats() {
		int[] allHours = new int[24];
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM usersHour");
			int count = 0;
	        while (rs.next()){
	        	allHours[count] = rs.getInt("totalUsers");
	        	count++;
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());			
		}
		return allHours;
	}
	public boolean incHour(int hour) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE usersHour SET totalUsers=totalUsers+1 WHERE hour="+hour);
			if (rs > 0){
				Auditor.Log(hour + " was inc", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
		return false;
	}
	public Vector<String> getUsersName() {
		Vector<String> result = new Vector<String>();
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM users");
	        while (rs.next()){
	        	result.add(rs.getString("username"));
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
        return result;
	}

}
